﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class NewsTypeDao
    {
        public bool Delete(SqlConnection conn, int typeId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Attachment] ");
            builder.Append("SET ");
            builder.Append("\t[DeleteFlag] = 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[AttachmentId] IN ");
            builder.Append("\t\t\t( ");
            builder.Append("\t\t\t\tSELECT ");
            builder.Append("\t\t\t\t\t[AttachmentId] ");
            builder.Append("\t\t\t\tFROM ");
            builder.Append("\t\t\t\t\t[dbo].[News] ");
            builder.Append("\t\t\t\tWHERE ");
            builder.Append("\t\t\t\t\t[TypeId] = @TypeId ");
            builder.Append("\t\t\t\t\tAND ISNULL([AttachmentId],'') <> '' ");
            builder.Append("\t\t\t) ");
            builder.Append("DELETE FROM [dbo].[News_Comment] ");
            builder.Append("WHERE ");
            builder.Append("\t[NewsId] IN (SELECT [NewsId] FROM [dbo].[News] WHERE [TypeId] = @TypeId) ");
            builder.Append("DELETE FROM [dbo].[News] ");
            builder.Append("WHERE ");
            builder.Append("\t[TypeId] = @TypeId ");
            builder.Append("DELETE FROM [dbo].[News_Type] ");
            builder.Append("WHERE ");
            builder.Append("\t[TypeId] = @TypeId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@TypeId", SqlDbType.Int) };
            commandParameters[0].Value = typeId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, NewsTypeData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[News_Type] ");
            builder.Append("( ");
            builder.Append("\t[TypeNo], ");
            builder.Append("\t[TypeName] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@TypeNo, ");
            builder.Append("\t@TypeName ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@TypeNo", SqlDbType.Int);
            commandParameters[0].Value = data.TypeNo;
            commandParameters[1] = new SqlParameter("@TypeName", SqlDbType.NVarChar, 50);
            commandParameters[1].Value = data.TypeName;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectAll(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[TypeId], ");
            builder.Append("\t[TypeNo], ");
            builder.Append("\t[TypeName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[News_Type] ");
            builder.Append("ORDER BY ");
            builder.Append("\t[TypeNo] ASC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "News_Type";
            return set.Tables[0];
        }

        public DataTable SelectByTypeId(SqlConnection conn, int typeId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[TypeId], ");
            builder.Append("\t[TypeNo], ");
            builder.Append("\t[TypeName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[News_Type] ");
            builder.Append("WHERE ");
            builder.Append("\t[TypeId] = @TypeId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@TypeId", SqlDbType.Int) };
            commandParameters[0].Value = typeId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "News_Type";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, NewsTypeData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[News_Type] ");
            builder.Append("SET ");
            builder.Append("\t[TypeNo] = @TypeNo, ");
            builder.Append("\t[TypeName] = @TypeName ");
            builder.Append("WHERE ");
            builder.Append("\t[TypeId] = @TypeId ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@TypeId", SqlDbType.Int);
            commandParameters[0].Value = data.TypeId;
            commandParameters[1] = new SqlParameter("@TypeNo", SqlDbType.Int);
            commandParameters[1].Value = data.TypeNo;
            commandParameters[2] = new SqlParameter("@TypeName", SqlDbType.NVarChar, 50);
            commandParameters[2].Value = data.TypeName;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

